from typing import Optional, List

from pydantic import TypeAdapter
from sqlalchemy import desc, asc
from sqlalchemy.orm import Session

from app.models.goods import Goods
from app.schemas.goods import PageGoodsList


def get_user_by_id(db: Session, goods_id: int) -> Optional[Goods]:
    """通过id查询"""
    return db.query(Goods).filter(Goods.goods_id == goods_id).first()


def page_goods_list(db: Session) -> list[PageGoodsList]:
    """首页查询"""
    goods_list = goods_list_by_page(db, 1, 1000)
    adapter = TypeAdapter(List[PageGoodsList])
    page_data: List[PageGoodsList] = adapter.validate_python(goods_list)
    return page_data


def goods_list_by_page(db: Session,
                       pageNo: int = 1,
                       pageSize: int = 10) -> List[Goods]:
    """分页查询商品"""
    # 计算偏移量
    offset = (pageNo - 1) * pageSize
    self = getQuery(db)
    return (self.order_by(Goods.goods_id.desc())  # 可选：排序
            .limit(pageSize).offset(offset).all())


def goods_list_by_count(db: Session) -> int:
    """统计条数"""
    self = getQuery(db)
    return self.count()


def getQuery(db):
    self = db.query(Goods)
    return self


def goods_list(
        db: Session,
        sortType: str,
        sortPrice: str,
        categoryId: int,
        goodsName: str,
        pageNo: int,
        pageSize: int
) -> list[type[Goods]]:
    offset = (pageNo - 1) * pageSize
    """分页查询商品"""
    self = goodsListQuery(db,
                          sortType,
                          sortPrice,
                          categoryId,
                          goodsName)
    return self.limit(pageSize).offset(offset).all()


def goodsListQuery(
        db: Session,
        sortType: str,
        sortPrice: str,
        categoryId: int,
        goodsName: str
):
    self = db.query(Goods)
    if goodsName:
        self = self.filter(Goods.goods_name.ilike(f"%{goodsName}%"))
    if categoryId:
        self = self.filter(Goods.category_id == categoryId)
    # 排序
    if not sortType or sortType == 'all':
        self = self.order_by(desc(Goods.goods_id))
    elif sortType == 'sales':
        self = self.order_by(desc(Goods.goods_sales))
    # 价格排序
    if sortPrice == 0:
        self = self.order_by(asc(Goods.goods_price_min))
    elif sortPrice == 1:
        self = self.order_by(desc(Goods.goods_price_min))
    return self


def goods_list_count(
        db: Session,
        sortType: str,
        sortPrice: str,
        categoryId: int,
        goodsName: str
) -> int:
    """分页查询商品"""
    self = goodsListQuery(db,
                          sortType,
                          sortPrice,
                          categoryId,
                          goodsName)
    return self.count()
